<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.ImageMapper">
    <resultMap id="image" type="models.entities.PictureEntity">
        <result property="id" column="imageId"/>
        <result property="filePath" column="filePath"/>
        <result property="md5" column="md5"/>
    </resultMap>
    <resultMap id="product" type="models.entities.ProductEntity">
        <result property="id" column="productId"/>
        <result property="title" column="title"/>
        <result property="description" column="description"/>
        <result property="price" column="price"/>
        <result property="discountPrice" column="discountPrice"/>
        <result property="defaultImageId" column="defaultImageId"/>
        <result property="imagesCount" column="imagesCount"/>
    </resultMap>
    <resultMap id="brand" type="models.entities.BrandEntity">
        <result property="id" column="brandId"/>
        <result property="title" column="title"/>
    </resultMap>
    <update id="readDuplicate">
        update
            image_duplicates
            set
                isNew = 0
        where id = #{id};
    </update>
    <select id="getDuplicates" resultType="models.entities.DuplicateEntity">
        select * from image_duplicates where isNew = 1;
    </select>
    <select id="getImagesWithoutMD5" resultMap="image">
        select * from images where md5 is null limit 100;
    </select>
    <select id="getImage" resultMap="image">
        select * from images where imageId = #{id};
    </select>
    <select id="getImagesWithoutMD5TotalCount" resultType="java.lang.Integer">
        select count(*) from images where md5 is null;
    </select>
    <update id="clearMD5">
        update images set md5 = null;
    </update>
    <update id="updateMD5" parameterType="models.entities.PictureEntity">
        update images set md5 = #{md5} where imageId = #{id};
    </update>
    <select id="getImageByMD5" resultMap="image">
        select * from images where md5 = #{md5} limit 1;
    </select>
    <select id="getProductsByImageId" resultMap="product">
        select * from products p, product_images pi where pi.productId = p.productId and pi.imageId = #{imageId};
    </select>
    <select id="getBrandsByImageId" resultMap="brand">
        select * from brands b, brand_images bi where bi.brandId = b.brandId and bi.imageId = #{imageId};
    </select>
    <insert id="addDuplicate">
        insert into image_duplicates(originalId, duplicateId) values(#{originalImageId}, #{duplicateImageId});
    </insert>
    <delete id="deleteImage">
        delete from product_images where imageId = #{imageId};
        delete from brand_images where imageId = #{imageId};
        delete from images where imageId = #{imageId};
    </delete>
    <update id="setOriginalImageForProductWithDuplicate">
        update
            product_images
                set imageId = #{existentImageId}
            where productId = #{productId} and imageId = #{originalId};
    </update>
    <delete id="deleteProductImage">
        delete from product_images where productId = #{productId} and imageId = #{imageId};
    </delete>
    <select id="getProductImage" resultMap="image">
        select * from images where imageId in
        (select imageId from product_images where productId = #{productId} and imageId = #{imageId});
    </select>
    <update id="changePath">
        update
            images
                set filePath = #{filePath}
            where imageId = #{id};
    </update>
    <update id="setOriginalImageForBrandWithDuplicate">
        update
            brand_images
                set imageId = #{originalId}
            where brandId = #{brandId} and imageId = #{existentImageId};
    </update>
</mapper>